﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;
using ZWL.DBUtility;//请先添加引用

namespace ZWL.BLL
{
    /// <summary>
    /// 集体固定资产
    /// </summary>
   public  class SC_JTGDZC
   {
       #region Model
       public SC_JTGDZC() { }
       private int _id;
       private string _ZCCODE;
       private string _ZCNAME;
       private string _GGXH;
       private string _JLDW;
       private float _DTSL;
       private float _FHSL;
       private string _CPJH;
       private string _ZZCJ;
       private string _CCBH;
       private DateTime _CCRQ;
       private DateTime _TCRQ;
       private string _ZYDW;
       private float _YZ;
       private float _JZ;
       private string _JZZB;
       private string _ZCZK;
       private DateTime _ZJSJ;
       private string _SFYJZJX;
       private string _JSZK;
       private string _AZDD;
       private string _ZRR;
       private string _BZ;

       /// <summary>
       /// 
       /// </summary>
       public int ID
       {
           set { _id = value; }
           get { return _id; }
       }

       /// <summary>
       /// 资产编码
       /// </summary>
       public string ZCCODE
       {
           get { return _ZCCODE; }
           set { _ZCCODE = value; }
       }
       
       /// <summary>
       /// 资产名称
       /// </summary>
       public string ZCNAME
       {
           get { return _ZCNAME; }
           set { _ZCNAME = value; }
       }
       
       /// <summary>
       /// 规格型号
       /// </summary>
       public string GGXH
       {
           get { return _GGXH; }
           set { _GGXH = value; }
       }
      
       /// <summary>
       /// 计量单位
       /// </summary>
       public string JLDW
       {
           get { return _JLDW; }
           set { _JLDW = value; }
       }
       
       /// <summary>
       /// 单台数量
       /// </summary>
       public float DTSL
       {
           get { return _DTSL; }
           set { _DTSL = value; }
       }
       
       /// <summary>
       /// 复合数量
       /// </summary>
       public float FHSL
       {
           get { return _FHSL; }
           set { _FHSL = value; }
       }
      
       /// <summary>
       /// 车牌井号
       /// </summary>
       public string CPJH
       {
           get { return _CPJH; }
           set { _CPJH = value; }
       }
      
       /// <summary>
       /// 制造厂家
       /// </summary>
       public string ZZCJ
       {
           get { return _ZZCJ; }
           set { _ZZCJ = value; }
       }
      
       /// <summary>
       /// 出厂编号
       /// </summary>
       public string CCBH
       {
           get { return _CCBH; }
           set { _CCBH = value; }
       }
      
       /// <summary>
       /// 出厂日期
       /// </summary>
       public DateTime CCRQ
       {
           get { return _CCRQ; }
           set { _CCRQ = value; }
       }
       
       /// <summary>
       /// 投产日期
       /// </summary>
       public DateTime TCRQ
       {
           get { return _TCRQ; }
           set { _TCRQ = value; }
       }
      
       /// <summary>
       /// 在用单位
       /// </summary>
       public string ZYDW
       {
           get { return _ZYDW; }
           set { _ZYDW = value; }
       }
      
       /// <summary>
       /// 原值
       /// </summary>
       public float YZ
       {
           get { return _YZ; }
           set { _YZ = value; }
       }
      
       /// <summary>
       /// 净值
       /// </summary>
       public float JZ
       {
           get { return _JZ; }
           set { _JZ = value; }
       }
       
       /// <summary>
       /// 减值准备
       /// </summary>
       public string JZZB
       {
           get { return _JZZB; }
           set { _JZZB = value; }
       }
      
       /// <summary>
       /// 资产状况
       /// </summary>
       public string ZCZK
       {
           get { return _ZCZK; }
           set { _ZCZK = value; }
       }
       
       /// <summary>
       /// 增加时间
       /// </summary>
       public DateTime ZJSJ
       {
           get { return _ZJSJ; }
           set { _ZJSJ = value; }
       }
       
       /// <summary>
       /// 是否有减值迹象
       /// </summary>
       public string SFYJZJX
       {
           get { return _SFYJZJX; }
           set { _SFYJZJX = value; }
       }
       /// <summary>
       /// 技术状况
       /// </summary>
       public string JSZK
       {
           get { return _JSZK; }
           set { _JSZK = value; }
       }
       /// <summary>
       /// 安装地点
       /// </summary>
       public string AZDD
       {
           get { return _AZDD; }
           set { _AZDD = value; }
       }
       /// <summary>
       /// ZRR
       /// </summary>
       public string ZRR
       {
           get { return _ZRR; }
           set { _ZRR = value; }
       }
       /// <summary>
       /// 备注
       /// </summary>
       public string BZ
       {
           get { return _BZ; }
           set { _BZ = value; }
       }
       #endregion Model
       #region  成员方法

    /// <summary>
        /// 得到一个对象实体
        /// </summary>
       public SC_JTGDZC(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,ZCCODE,ZCNAME,GGXH,JLDW,DTSL,FHSL,CPJH,ZZCJ,CCBH,CCRQ,TCRQ,ZYDW,YZ,JZ,JZZB,ZCZK,ZJSJ,SFYJZJX,JSZK,AZDD,ZRR,BZ ");
            strSql.Append(" FROM SC_JTGDZC ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                ZCCODE = ds.Tables[0].Rows[0]["ZCCODE"].ToString() != "" ? ds.Tables[0].Rows[0]["ZCCODE"].ToString() : "";
                ZCNAME = ds.Tables[0].Rows[0]["ZCNAME"].ToString() != "" ? ds.Tables[0].Rows[0]["ZCNAME"].ToString() : "";
                GGXH = ds.Tables[0].Rows[0]["GGXH"].ToString() != "" ? ds.Tables[0].Rows[0]["GGXH"].ToString() : "";
                JLDW = ds.Tables[0].Rows[0]["JLDW"].ToString() != "" ? ds.Tables[0].Rows[0]["JLDW"].ToString() : "";
                DTSL = ds.Tables[0].Rows[0]["DTSL"].ToString() != "" ? float.Parse(ds.Tables[0].Rows[0]["DTSL"].ToString()) : 0;
                FHSL = ds.Tables[0].Rows[0]["FHSL"].ToString() != "" ? float.Parse(ds.Tables[0].Rows[0]["FHSL"].ToString()) : 0;
                CPJH = ds.Tables[0].Rows[0]["CPJH"].ToString() != "" ? ds.Tables[0].Rows[0]["CPJH"].ToString() : "";
                ZZCJ = ds.Tables[0].Rows[0]["ZZCJ"].ToString() != "" ? ds.Tables[0].Rows[0]["ZZCJ"].ToString() : "";
                CCBH = ds.Tables[0].Rows[0]["CCBH"].ToString() != "" ? ds.Tables[0].Rows[0]["CCBH"].ToString() : "";
                if (ds.Tables[0].Rows[0]["CCRQ"].ToString() != "")
                {
                    CCRQ=Convert.ToDateTime(ds.Tables[0].Rows[0]["CCRQ"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TCRQ"].ToString() != "")
                {
                    TCRQ = Convert.ToDateTime(ds.Tables[0].Rows[0]["TCRQ"].ToString());
                }
                ZYDW = ds.Tables[0].Rows[0]["ZYDW"].ToString() != "" ? ds.Tables[0].Rows[0]["ZYDW"].ToString() : "";
                YZ = ds.Tables[0].Rows[0]["YZ"].ToString() != "" ? float.Parse(ds.Tables[0].Rows[0]["YZ"].ToString()) : 0;
                JZ = ds.Tables[0].Rows[0]["JZ"].ToString() != "" ? float.Parse(ds.Tables[0].Rows[0]["JZ"].ToString()) : 0;
                JZZB = ds.Tables[0].Rows[0]["JZZB"].ToString() != "" ? ds.Tables[0].Rows[0]["JZZB"].ToString() : "";
                ZCZK = ds.Tables[0].Rows[0]["ZCZK"].ToString() != "" ? ds.Tables[0].Rows[0]["ZCZK"].ToString() : "";
                if (ds.Tables[0].Rows[0]["ZJSJ"].ToString() != "")
                {
                    ZJSJ = Convert.ToDateTime(ds.Tables[0].Rows[0]["ZJSJ"].ToString());
                }
                SFYJZJX = ds.Tables[0].Rows[0]["SFYJZJX"].ToString() != "" ? ds.Tables[0].Rows[0]["SFYJZJX"].ToString() : "";
                JSZK = ds.Tables[0].Rows[0]["JSZK"].ToString() != "" ? ds.Tables[0].Rows[0]["JSZK"].ToString() : "";
                AZDD = ds.Tables[0].Rows[0]["AZDD"].ToString() != "" ? ds.Tables[0].Rows[0]["AZDD"].ToString() : "";
                ZRR = ds.Tables[0].Rows[0]["ZRR"].ToString() != "" ? ds.Tables[0].Rows[0]["ZRR"].ToString() : "";
                BZ = ds.Tables[0].Rows[0]["BZ"].ToString() != "" ? ds.Tables[0].Rows[0]["BZ"].ToString() : "";
                
               

            }
        }

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ID", "SC_JTGDZC");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from SC_JTGDZC");
            strSql.Append(" where ID=@ID ");

            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SC_JTGDZC(");
            strSql.Append("ZCCODE,ZCNAME,GGXH,JLDW,DTSL,FHSL,CPJH,ZZCJ,CCBH,CCRQ,TCRQ,ZYDW,YZ,JZ,JZZB,ZCZK,ZJSJ,SFYJZJX,JSZK,AZDD,ZRR,BZ)");
            strSql.Append(" values (");
            strSql.Append("@ZCCODE,@ZCNAME,@GGXH,@JLDW,@DTSL,@FHSL,@CPJH,@ZZCJ,@CCBH,@CCRQ,@TCRQ,@ZYDW,@YZ,@JZ,@JZZB,@ZCZK,@ZJSJ,@SFYJZJX,@JSZK,@AZDD,@ZRR,@BZ)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@ZCCODE", SqlDbType.VarChar,50),
					new SqlParameter("@ZCNAME", SqlDbType.VarChar,50),
					new SqlParameter("@GGXH", SqlDbType.VarChar,50),
					new SqlParameter("@JLDW", SqlDbType.VarChar,50),
					new SqlParameter("@DTSL", SqlDbType.Float,10),
					new SqlParameter("@FHSL", SqlDbType.Float,10),
					new SqlParameter("@CPJH", SqlDbType.VarChar,50),
					new SqlParameter("@ZZCJ", SqlDbType.VarChar,50),
					new SqlParameter("@CCBH", SqlDbType.VarChar,50),
                    new SqlParameter("@CCRQ", SqlDbType.DateTime),
                    new SqlParameter("@TCRQ", SqlDbType.DateTime),
                    new SqlParameter("@ZYDW", SqlDbType.VarChar,50),
                    new SqlParameter("@YZ", SqlDbType.Float,10),
                    new SqlParameter("@JZ", SqlDbType.Float,10),
                    new SqlParameter("@JZZB", SqlDbType.VarChar,50),
                    new SqlParameter("@ZCZK", SqlDbType.VarChar,50),
                    new SqlParameter("@ZJSJ", SqlDbType.DateTime),
                    new SqlParameter("@SFYJZJX", SqlDbType.VarChar,50),
                    new SqlParameter("@JSZK", SqlDbType.VarChar,50),
                    new SqlParameter("@AZDD", SqlDbType.VarChar,50),
                    new SqlParameter("@ZRR", SqlDbType.VarChar,50),
                    new SqlParameter("@BZ", SqlDbType.VarChar,100)};

            parameters[0].Value = ZCCODE;
            parameters[1].Value = ZCNAME;
            parameters[2].Value = GGXH;
            parameters[3].Value = JLDW;
            parameters[4].Value = DTSL;
            parameters[5].Value = FHSL;
            parameters[6].Value = CPJH;
            parameters[7].Value = ZZCJ;
            parameters[8].Value = CCBH;
            parameters[9].Value = CCRQ;
            parameters[10].Value = TCRQ;
            parameters[11].Value = ZYDW;
            parameters[12].Value = YZ;
            parameters[13].Value = JZ;
            parameters[14].Value = JZZB;
            parameters[15].Value = ZCZK;
            parameters[16].Value = ZJSJ;
            parameters[17].Value = SFYJZJX;
            parameters[18].Value = JSZK;
            parameters[19].Value = AZDD;
            parameters[20].Value = ZRR;
            parameters[21].Value = BZ;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 1;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public void Update()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update SC_JTGDZC set ");
            strSql.Append("ZCCODE=@ZCCODE,");
            strSql.Append("ZCNAME=@ZCNAME,");
            strSql.Append("GGXH=@GGXH,");
            strSql.Append("DTSL=@DTSL,");
            strSql.Append("FHSL=@FHSL,");
            strSql.Append("CPJH=@CPJH,");
            strSql.Append("ZZCJ=@ZZCJ,");
            strSql.Append("CCBH=@CCBH,");
            strSql.Append("CCRQ=@CCRQ");
            strSql.Append("TCRQ=@TCRQ");
            strSql.Append("ZYDW=@ZYDW");
            strSql.Append("YZ=@YZ");
            strSql.Append("JZ=@JZ");
            strSql.Append("JZZB=@JZZB");
            strSql.Append("ZCZK=@ZCZK");
            strSql.Append("ZJSJ=@ZJSJ");
            strSql.Append("SFYJZJX=@SFYJZJX");
            strSql.Append("JSZK=@JSZK");
            strSql.Append("AZDD=@AZDD");
            strSql.Append("ZRR=@ZRR");
            strSql.Append("BZ=@BZ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ZCCODE", SqlDbType.VarChar,50),
					new SqlParameter("@ZCNAME", SqlDbType.VarChar,50),
					new SqlParameter("@GGXH", SqlDbType.VarChar,50),
					new SqlParameter("@JLDW", SqlDbType.VarChar,50),
					new SqlParameter("@DTSL", SqlDbType.Float,10),
					new SqlParameter("@FHSL", SqlDbType.Float,10),
					new SqlParameter("@CPJH", SqlDbType.VarChar,50),
					new SqlParameter("@ZZCJ", SqlDbType.VarChar,50),
					new SqlParameter("@CCBH", SqlDbType.VarChar,50),
                    new SqlParameter("@CCRQ", SqlDbType.DateTime),
                    new SqlParameter("@TCRQ", SqlDbType.DateTime),
                    new SqlParameter("@ZYDW", SqlDbType.VarChar,50),
                    new SqlParameter("@YZ", SqlDbType.Float,10),
                    new SqlParameter("@JZ", SqlDbType.Float,10),
                    new SqlParameter("@JZZB", SqlDbType.VarChar,50),
                    new SqlParameter("@ZCZK", SqlDbType.VarChar,50),
                    new SqlParameter("@ZJSJ", SqlDbType.DateTime),
                    new SqlParameter("@SFYJZJX", SqlDbType.VarChar,50),
                    new SqlParameter("@JSZK", SqlDbType.VarChar,50),
                    new SqlParameter("@AZDD", SqlDbType.VarChar,50),
                    new SqlParameter("@ZRR", SqlDbType.VarChar,50),
                    new SqlParameter("@BZ", SqlDbType.VarChar,100)};
            parameters[0].Value = ID;
            parameters[1].Value = ZCCODE;
            parameters[2].Value = ZCNAME;
            parameters[3].Value = GGXH;
            parameters[4].Value = JLDW;
            parameters[5].Value = DTSL;
            parameters[6].Value = FHSL;
            parameters[7].Value = CPJH;
            parameters[8].Value = ZZCJ;
            parameters[8].Value = CCBH;
            parameters[10].Value = CCRQ;
            parameters[11].Value = TCRQ;
            parameters[12].Value = ZYDW;
            parameters[13].Value = YZ;
            parameters[14].Value = JZ;
            parameters[15].Value = JZZB;
            parameters[16].Value = ZCZK;
            parameters[17].Value = ZJSJ;
            parameters[18].Value = SFYJZJX;
            parameters[18].Value = JSZK;
            parameters[19].Value = AZDD;
            parameters[20].Value = ZRR;
            parameters[21].Value = BZ;


            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 删除一条数据
        /// </summary>
        public void Delete(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from SC_JTGDZC ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public void GetModel(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,ZCCODE,ZCNAME,GGXH,JLDW,DTSL,FHSL,CPJH,ZZCJ,CCBH,CCRQ,TCRQ,ZYDW,YZ,JZ,JZZB,ZCZK,ZJSJ,SFYJZJX,JSZK,AZDD,ZRR,BZ ");
            strSql.Append(" FROM SC_JTGDZC ");
            strSql.Append(" where ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                ZCCODE = ds.Tables[0].Rows[0]["ZCCODE"].ToString() != "" ? ds.Tables[0].Rows[0]["ZCCODE"].ToString() : "";
                ZCNAME = ds.Tables[0].Rows[0]["ZCNAME"].ToString() != "" ? ds.Tables[0].Rows[0]["ZCNAME"].ToString() : "";
                GGXH = ds.Tables[0].Rows[0]["GGXH"].ToString() != "" ? ds.Tables[0].Rows[0]["GGXH"].ToString() : "";
                JLDW = ds.Tables[0].Rows[0]["JLDW"].ToString() != "" ? ds.Tables[0].Rows[0]["JLDW"].ToString() : "";
                DTSL = ds.Tables[0].Rows[0]["DTSL"].ToString() != "" ? float.Parse(ds.Tables[0].Rows[0]["DTSL"].ToString()) : 0;
                FHSL = ds.Tables[0].Rows[0]["FHSL"].ToString() != "" ? float.Parse(ds.Tables[0].Rows[0]["FHSL"].ToString()) : 0;
                CPJH = ds.Tables[0].Rows[0]["CPJH"].ToString() != "" ? ds.Tables[0].Rows[0]["CPJH"].ToString() : "";
                ZZCJ = ds.Tables[0].Rows[0]["ZZCJ"].ToString() != "" ? ds.Tables[0].Rows[0]["ZZCJ"].ToString() : "";
                CCBH = ds.Tables[0].Rows[0]["CCBH"].ToString() != "" ? ds.Tables[0].Rows[0]["CCBH"].ToString() : "";
                if (ds.Tables[0].Rows[0]["CCRQ"].ToString() != "")
                {
                    CCRQ = Convert.ToDateTime(ds.Tables[0].Rows[0]["CCRQ"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TCRQ"].ToString() != "")
                {
                    TCRQ = Convert.ToDateTime(ds.Tables[0].Rows[0]["TCRQ"].ToString());
                }
                ZYDW = ds.Tables[0].Rows[0]["ZYDW"].ToString() != "" ? ds.Tables[0].Rows[0]["ZYDW"].ToString() : "";
                YZ = ds.Tables[0].Rows[0]["YZ"].ToString() != "" ? float.Parse(ds.Tables[0].Rows[0]["YZ"].ToString()) : 0;
                JZ = ds.Tables[0].Rows[0]["JZ"].ToString() != "" ? float.Parse(ds.Tables[0].Rows[0]["JZ"].ToString()) : 0;
                JZZB = ds.Tables[0].Rows[0]["JZZB"].ToString() != "" ? ds.Tables[0].Rows[0]["JZZB"].ToString() : "";
                ZCZK = ds.Tables[0].Rows[0]["ZCZK"].ToString() != "" ? ds.Tables[0].Rows[0]["ZCZK"].ToString() : "";
                if (ds.Tables[0].Rows[0]["ZJSJ"].ToString() != "")
                {
                    ZJSJ = Convert.ToDateTime(ds.Tables[0].Rows[0]["ZJSJ"].ToString());
                }
                SFYJZJX = ds.Tables[0].Rows[0]["SFYJZJX"].ToString() != "" ? ds.Tables[0].Rows[0]["SFYJZJX"].ToString() : "";
                JSZK = ds.Tables[0].Rows[0]["JSZK"].ToString() != "" ? ds.Tables[0].Rows[0]["JSZK"].ToString() : "";
                AZDD = ds.Tables[0].Rows[0]["AZDD"].ToString() != "" ? ds.Tables[0].Rows[0]["AZDD"].ToString() : "";
                ZRR = ds.Tables[0].Rows[0]["ZRR"].ToString() != "" ? ds.Tables[0].Rows[0]["ZRR"].ToString() : "";
                BZ = ds.Tables[0].Rows[0]["BZ"].ToString() != "" ? ds.Tables[0].Rows[0]["BZ"].ToString() : "";
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM SC_JTGDZC ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// Excel数据导入Datable
        /// </summary>
        /// <param name="fileUrl"></param>
        /// <param name="table"></param>
        /// <returns></returns>
        public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)
        {
            //office2007之前 仅支持.xls
            //const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            System.Data.DataTable dt = null;
            //建立连接
            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //获取Excel的第一个Sheet名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
                //查询sheet中的数据
                string strSql = "select * from [" + sheetName + "]";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds, table);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception exc)
            {
                throw exc;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// SqlBulkCopy导入数据到数据库
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public void SqlBulkCopyInsetData(System.Data.DataTable dt)
        {


            //数据库连接
            string strConnection = ConfigurationManager.ConnectionStrings["BDOAConnectionString"].ToString();
            SqlConnection sqlConnection = new SqlConnection(strConnection);
            sqlConnection.Open();

            //声明SqlBulkCopy ,using释放非托管资源
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(strConnection))
            {
                //一次批量的插入的数据量
                sqlBC.BatchSize = 1000;
                //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                sqlBC.BulkCopyTimeout = 60;

                //设置要批量写入的表
                sqlBC.DestinationTableName = "SC_JTGDZC";

                //自定义的datatable和数据库的字段进行对应
                sqlBC.ColumnMappings.Add("资产编码", "ZCCODE");
                sqlBC.ColumnMappings.Add("资产名称", "ZCNAME");
                sqlBC.ColumnMappings.Add("规格型号", "GGXH");
                sqlBC.ColumnMappings.Add("计量单位", "JLDW");
                sqlBC.ColumnMappings.Add("单台数量", "DTSL");
                sqlBC.ColumnMappings.Add("复合数量", "FHSL");
                sqlBC.ColumnMappings.Add("车牌井号", "CPJH");
                sqlBC.ColumnMappings.Add("制造厂家", "ZZCJ");
                sqlBC.ColumnMappings.Add("出厂编号", "CCBH");
                sqlBC.ColumnMappings.Add("出厂日期", "CCRQ");
                sqlBC.ColumnMappings.Add("投产日期", "TCRQ");
                sqlBC.ColumnMappings.Add("在用单位", "ZYDW");
                sqlBC.ColumnMappings.Add("原值", "YZ");
                sqlBC.ColumnMappings.Add("净值", "JZ");
                sqlBC.ColumnMappings.Add("减值准备", "JZZB");
                sqlBC.ColumnMappings.Add("资产状况", "ZCZK");
                sqlBC.ColumnMappings.Add("增加时间", "ZJSJ");
                sqlBC.ColumnMappings.Add("是否有减值迹象", "SFYJZJX");
                sqlBC.ColumnMappings.Add("技术状况", "JSZK");
                sqlBC.ColumnMappings.Add("安装地点", "AZDD");
                sqlBC.ColumnMappings.Add("责任人", "ZRR");
                sqlBC.ColumnMappings.Add("备注", "BZ");

                //批量写入
                sqlBC.WriteToServer(dt);
            }
            sqlConnection.Dispose();

        }



        #endregion  成员方法


    }
}
